﻿Imports System.Data.SqlClient
Public Class frmREvaluaciones
    Dim ExistenRegistros As Boolean
    Private Sub frmREvaluaciones_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ToolStrip1.Cursor = Cursors.Hand
        ToolStrip1.RenderMode = System.Windows.Forms.ToolStripRenderMode.System

        Me.lstMeses.Text = ""
        ExistenRegistros = False

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


    End Sub
    Sub Integrada()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 6
        ExistenRegistros = False

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15
        Dim strSql As String = ""
        Dim cont As Integer = 0
        Dim i As Integer = 0

        'Segundo, obtener los registros de gratificaciones
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection
        Dim anio As Integer = Now.Year
        strSql = "select a.idEvaluacionTotales,evaluacion,mes,anio,licencia,a.tarjeton,uniforme,limpieza,vueltas,turnos,frecuencia,disciplina,liquidacion,total,a.idempleado,NombreCompleto,EstadoEmpleado,rendimiento,cuidado,manejo,trato,disponibilidad"
        strSql = strSql & " from EvaluacionOLDTotales a INNER JOIN Empleados b"
        strSql = strSql & " ON a.idempleado=b.idempleado WHERE mes='" & Me.lstMeses.Text.Trim & "' and anio=" & anio
        strSql = strSql & " order by total DESC,a.idempleado"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        wb.SHEETS(1).cells(rng, 1).value = "Tarjeton"
        wb.SHEETS(1).cells(rng, 2).value = "Paterno Materno Nombre"
        wb.SHEETS(1).cells(rng, 3).value = "Status"
        wb.SHEETS(1).cells(rng, 4).value = "Licencia"
        wb.SHEETS(1).cells(rng, 5).value = "Tarjeton"
        wb.SHEETS(1).cells(rng, 6).value = "Uniforme"
        wb.SHEETS(1).cells(rng, 7).value = "Limpieza"
        wb.SHEETS(1).cells(rng, 8).value = "Vueltas"
        wb.SHEETS(1).cells(rng, 9).value = "Turnos"
        wb.SHEETS(1).cells(rng, 10).value = "Frecuencias"
        wb.SHEETS(1).cells(rng, 11).value = "Disciplina"
        wb.SHEETS(1).cells(rng, 12).value = "Liquidación"
        wb.SHEETS(1).cells(rng, 13).value = "Rendimiento"
        wb.SHEETS(1).cells(rng, 14).value = "Cuidado Uni."
        wb.SHEETS(1).cells(rng, 15).value = "Manejo"
        wb.SHEETS(1).cells(rng, 16).value = "Trato"
        wb.SHEETS(1).cells(rng, 17).value = "Disponibilidad"
        wb.SHEETS(1).cells(rng, 18).value = "Total"
        For i = 1 To 18
            wb.SHEETS(1).cells(rng, i).font.size = 9
            wb.SHEETS(1).cells(rng, i).font.bold = True
            wb.SHEETS(1).cells(rng, i).interior.colorindex = 1
            wb.SHEETS(1).cells(rng, i).font.colorindex = 2
        Next
        For i = 4 To 18
            wb.SHEETS(1).columns(i).numberformat = "#,##0.00"
        Next
        rng = rng + 1
        Dim statuss As String

        Do While rdBuscar.Read()
            ExistenRegistros = True
            cont = cont + 1
            wb.SHEETS(1).cells(rng, 1).value = BuscarStrDatoCitra("Capacitacion", "tarjeton", "idempleado=" & rdBuscar("idempleado"))
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("NombreCompleto")
            statuss = rdBuscar("EstadoEmpleado").ToString.Trim
            Select Case statuss
                Case "A"
                    statuss = "Activo"
                Case "B"
                    statuss = "Baja"
                Case "R"
                    statuss = "Activo"
            End Select
            wb.SHEETS(1).cells(rng, 3).value = statuss
            wb.SHEETS(1).cells(rng, 4).value = rdBuscar("Licencia") / 2
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("tarjeton") / 2
            wb.SHEETS(1).cells(rng, 6).value = rdBuscar("uniforme") / 2
            wb.SHEETS(1).cells(rng, 7).value = rdBuscar("limpieza") / 2
            wb.SHEETS(1).cells(rng, 8).value = rdBuscar("vueltas") / 2
            wb.SHEETS(1).cells(rng, 9).value = rdBuscar("turnos") / 2
            wb.SHEETS(1).cells(rng, 10).value = rdBuscar("frecuencia") / 2
            wb.SHEETS(1).cells(rng, 11).value = rdBuscar("disciplina") / 2
            wb.SHEETS(1).cells(rng, 12).value = rdBuscar("liquidacion") / 2
            wb.SHEETS(1).cells(rng, 13).value = rdBuscar("rendimiento") / 2
            wb.SHEETS(1).cells(rng, 14).value = rdBuscar("cuidado") / 2
            wb.SHEETS(1).cells(rng, 15).value = rdBuscar("manejo") / 2
            wb.SHEETS(1).cells(rng, 16).value = rdBuscar("trato") / 2
            wb.SHEETS(1).cells(rng, 17).value = rdBuscar("disponibilidad") / 2
            wb.SHEETS(1).cells(rng, 18).value = rdBuscar("total") / 2
            For i = 1 To 18
                wb.SHEETS(1).cells(rng, i).font.size = 9
            Next
            rng = rng + 1
        Loop

        If ExistenRegistros = False Then
            wb.SHEETS(1).cells(rng, 1).value = "No se encontrarón registros para mostrar"
        End If



        wb.SHEETS(1).COLUMNS(1).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 30
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(4).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(5).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(6).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(7).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(8).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(9).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(10).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(11).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(12).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(13).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(14).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(15).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(16).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(17).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(18).ColumnWidth = 10

        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Reporte de Evaluación a Operadores del Mes de " & Me.lstMeses.Text.Trim
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & usuarioactivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
        wb.SHEETS(1).cells(4, 1).value = "Registros del filtro: " & cont
        wb.SHEETS(1).cells(4, 1).FONT.SIZE = 8
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


    End Sub
    Sub NuevaEvaluacion()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 6
        ExistenRegistros = False

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15
        Dim strSql As String = ""
        Dim cont As Integer = 0
        Dim i As Integer = 0

        'Segundo, obtener los registros de gratificaciones
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection
        Dim anio As Integer = Now.Year
        strSql = "select mes,anio,Licencia,Tarjeton,Uniforme,Limpieza,Vueltas,Turnos,Frecuencia,Disciplina,Liquidacion,Total,A.IDEmpleado,NombreCompleto,EstadoEmpleado"
        strSql = strSql & " from EvaluacionOLDTotales A INNER JOIN Empleados B ON A.IdEmpleado=B.IdEmpleado"
        strSql = strSql & " where Mes='" & Me.lstMeses.Text.Trim & "' and anio=" & anio
        strSql = strSql & " order by total DESC,a.idempleado"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        wb.SHEETS(1).cells(rng, 1).value = "Tarjeton"
        wb.SHEETS(1).cells(rng, 2).value = "Paterno Materno Nombre"
        wb.SHEETS(1).cells(rng, 3).value = "Status"
        wb.SHEETS(1).cells(rng, 4).value = "Licencia"
        wb.SHEETS(1).cells(rng, 5).value = "Tarjeton"
        wb.SHEETS(1).cells(rng, 6).value = "Uniforme"
        wb.SHEETS(1).cells(rng, 7).value = "Limpieza"
        wb.SHEETS(1).cells(rng, 8).value = "Vueltas"
        wb.SHEETS(1).cells(rng, 9).value = "Turnos"
        wb.SHEETS(1).cells(rng, 10).value = "Frecuencias"
        wb.SHEETS(1).cells(rng, 11).value = "Disciplina"
        wb.SHEETS(1).cells(rng, 12).value = "Liquidación"
        wb.SHEETS(1).cells(rng, 13).value = "Total"
        For i = 1 To 13
            wb.SHEETS(1).cells(rng, i).font.size = 9
            wb.SHEETS(1).cells(rng, i).font.bold = True
            wb.SHEETS(1).cells(rng, i).interior.colorindex = 1
            wb.SHEETS(1).cells(rng, i).font.colorindex = 2
        Next
        For i = 4 To 13
            wb.SHEETS(1).columns(i).numberformat = "#,##0.00"
        Next
        rng = rng + 1
        Dim statuss As String

        Do While rdBuscar.Read()
            ExistenRegistros = True
            cont = cont + 1
            wb.SHEETS(1).cells(rng, 1).value = BuscarStrDatoCitra("Capacitacion", "tarjeton", "idempleado=" & rdBuscar("idempleado"))
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("NombreCompleto")
            statuss = rdBuscar("EstadoEmpleado").ToString.Trim
            Select Case statuss
                Case "A"
                    statuss = "Activo"
                Case "B"
                    statuss = "Baja"
                Case "R"
                    statuss = "Activo"
            End Select
            wb.SHEETS(1).cells(rng, 3).value = statuss
            wb.SHEETS(1).cells(rng, 4).value = rdBuscar("Licencia").ToString.Trim
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("Tarjeton")
            wb.SHEETS(1).cells(rng, 6).value = rdBuscar("Uniforme").ToString.Trim
            wb.SHEETS(1).cells(rng, 7).value = rdBuscar("Limpieza").ToString.Trim
            wb.SHEETS(1).cells(rng, 8).value = rdBuscar("Vueltas").ToString.Trim
            wb.SHEETS(1).cells(rng, 9).value = rdBuscar("Turnos").ToString.Trim
            wb.SHEETS(1).cells(rng, 10).value = rdBuscar("Frecuencia").ToString.Trim
            wb.SHEETS(1).cells(rng, 11).value = rdBuscar("Disciplina").ToString.Trim
            wb.SHEETS(1).cells(rng, 12).value = rdBuscar("Liquidacion").ToString.Trim
            wb.SHEETS(1).cells(rng, 13).value = rdBuscar("total").ToString.Trim
            For i = 1 To 13
                wb.SHEETS(1).cells(rng, i).font.size = 9
            Next
            rng = rng + 1
        Loop

        If ExistenRegistros = False Then
            wb.SHEETS(1).cells(rng, 1).value = "No se encontrarón registros para mostrar"
        End If



        wb.SHEETS(1).COLUMNS(1).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 30
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(4).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(5).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(6).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(7).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(8).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(9).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(10).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(11).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(12).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(13).ColumnWidth = 10

        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Reporte de Evaluación a Operadores del Mes de " & Me.lstMeses.Text.Trim
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & UsuarioActivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
        wb.SHEETS(1).cells(4, 1).value = "Registros del filtro: " & cont
        wb.SHEETS(1).cells(4, 1).FONT.SIZE = 8

    End Sub

    Sub todosDos()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 6
        ExistenRegistros = False

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15
        'Primero Obtener todos los nombres de los empleados
        'Dim cnConnNOM As New SqlClient.SqlConnection
        'cnConnNOM.ConnectionString = gstrConnectionNomipaqI_SQL

        Dim strSql As String = ""
        Dim cont As Integer = 0
        Dim i As Integer = 0

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection
        Dim anio As Integer = Now.Year
        strSql = "select idEvaluacionInternaTotales, a.idempleado,mes,anio,total,NombreCompleto, EstadoEmpleado,Descripcion"
        strSql = strSql & " from EvaluacionInternaTotales a INNER JOIN Empleados b"
        strSql = strSql & " ON a.idempleado=b.idempleado INNER JOIN Departamentos d ON b.IdDepartamento=d.IdDepartamento and mes='" & Me.lstMeses.Text.Trim & "' and anio=" & anio
        strSql = strSql & " order by total DESC,a.idempleado"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        wb.SHEETS(1).cells(rng, 1).value = "Nombre"
        wb.SHEETS(1).cells(rng, 2).value = "Depatamento"
        wb.SHEETS(1).cells(rng, 3).value = "Total"

        For i = 1 To 3
            wb.SHEETS(1).cells(rng, i).font.size = 9
            wb.SHEETS(1).cells(rng, i).font.bold = True
            wb.SHEETS(1).cells(rng, i).interior.colorindex = 1
            wb.SHEETS(1).cells(rng, i).font.colorindex = 2
        Next

        wb.SHEETS(1).columns(3).numberformat = "#,##0.00"

        rng = rng + 1

        Do While rdBuscar.Read()
            ExistenRegistros = True
            cont = cont + 1

            wb.SHEETS(1).cells(rng, 1).value = rdBuscar("NombreCompleto").ToString.Trim
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("descripcion").ToString.Trim
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("total").ToString.Trim
            For i = 1 To 3
                wb.SHEETS(1).cells(rng, i).font.size = 9
            Next
            rng = rng + 1
        Loop

        If ExistenRegistros = False Then
            wb.SHEETS(1).cells(rng, 1).value = "No se encontrarón registros para mostrar"
        End If



        wb.SHEETS(1).COLUMNS(1).ColumnWidth = 30
        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 20
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 10

        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Resumen de la evaluación interna de Operadores " & Me.lstMeses.Text.Trim & " del " & Now.Year
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & UsuarioActivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
        wb.SHEETS(1).cells(4, 1).value = "Registros del filtro: " & cont
        wb.SHEETS(1).cells(4, 1).FONT.SIZE = 8
    End Sub
    Private Sub RadButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()
    End Sub

    Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
        If Me.lstMeses.Text = "" Then
            MsgBox("Debes indicar un bimestre", MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
        End If
        NuevaEvaluacion()

    End Sub

    Private Sub ToolStripButton1_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton1.Click
        Close()

    End Sub
End Class